package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import es.ats.codington.festival.domain.EventSignUp;
import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.interfaces.daos.IEvenSignUpDAO;
//import es.ats.codington.festival.utils.JSonQuery;

public class EventSignUpDAOImpl implements IEvenSignUpDAO{

	//-------------------------------------
	
	private Connection connection = null;
	private PreparedStatement statement = null;
	private ResultSet resultSet = null;
	//JSonQuery json=new JSonQuery();

	/**
	 * --------------------------Constructor------------
	 * @param connection
	 */
	public EventSignUpDAOImpl(Connection connection){
		this.connection=connection;
	}
	
	//--------------------------- Methods-------------------
	
	/**
	 * 
	 * @param e
	 * @return
	 * @throws SQLException
	 */
	public int registerVisitorForNewEventDAO(EventSignUp e) throws SQLException{

		int affectedRows = 0;

		try{	
			//statement = connection.prepareStatement(json.getregVisForEvent());
			statement = connection.prepareStatement("INSERT INTO eventsignup (idEvent,idVisitor) VALUES (?,?)");
			statement.setInt(1, e.getIdEvent());
			statement.setInt(2, e.getIdVisitor());
			
			affectedRows = statement.executeUpdate();
			
		} catch (Exception ee) {
			System.out.println("registerVisitorForNewEvent 1: "+ ee.getMessage());
			return affectedRows;
			}
	 
		return affectedRows;
	}
	
	/**
	 * 
	 * @param e
	 * @return
	 * @throws SQLException
	 */
	public boolean checkEventsofVisitorDAO(EventSignUp e) throws SQLException{
		int fila =0;
		try{	
			
			//statement = connection.prepareStatement(json.getcheckEventsofVisitor());
			statement = connection.prepareStatement("SELECT idEvent FROM eventsignup WHERE idEvent=? AND idVisitor=?");
			statement.setInt(1, e.getIdEvent());
			statement.setInt(2, e.getIdVisitor());

			resultSet = statement.executeQuery();
			
			resultSet.last();
			fila = resultSet.getRow();
			resultSet.beforeFirst();
						
		}catch(Exception eee){
			eee.getMessage();
		}
			
		return fila==1;
	}
	
	/**
	 * 
	 * @param e
	 * @return
	 * @throws SQLException
	 */
	public int unregisterVisitorForEventDAO(EventSignUp e) throws SQLException{
		int affectedRows = 0;

		try{		    		
		
			//statement = connection.prepareStatement(json.getunregisterVisitorForEvent());
			statement = connection.prepareStatement("DELETE FROM eventsignup WHERE idEvent=? AND idVisitor=?");
			statement.setInt(1, e.getIdEvent());
			statement.setInt(2, e.getIdVisitor());

			affectedRows = statement.executeUpdate();

		} catch (Exception ee) {
			System.out.println("unregisterVisitorForEvent 1: "+ ee.getMessage());
			return affectedRows;

		} 

		return affectedRows;
	}
	
	/**
	 * 
	 * @param e
	 * @return
	 * @throws SQLException
	 */
	public List<Event> viewEventsOfUserDAO(EventSignUp e) throws SQLException{
		
		List<Event> events = new ArrayList<Event>();
		
		try{		    		


			//statement = connection.prepareStatement(json.getviewEventsOfUser());
			statement = connection.prepareStatement("SELECT event.* FROM event,eventsignup WHERE idvisitor=? AND event.idEvent=eventsignup.idEvent");
			statement.setInt(1, e.getIdVisitor());
		    
		    resultSet =  statement.executeQuery();
		    
		    while (resultSet.next()) {
				Event event = new Event();

				event.setIdEvent(resultSet.getInt("idEvent"));
				event.setEventName(resultSet.getString("eventName"));
				event.setDescription(resultSet.getString("description"));
				event.setDuration(resultSet.getInt("duration"));
				event.setEventType(resultSet.getString("eventType"));
				event.setSeatsAvailable(resultSet.getInt("seatsAvailable"));
				event.setSeatsTotal(resultSet.getInt("seatsTotal"));
				event.setPlace(resultSet.getString("place"));

				events.add(event);
			}
		    
		    
		    //events = this.showAllEventsDAO(resultSet);


		} catch (Exception ee) {
			System.out.println("viewEventsOfUser 1: "+ ee.getMessage());
			return events;

		} 

		return events;
	}
	
public boolean updateMinusSeatsAvailableEventDAO(EventSignUp e){
		
		int affectedRows = 0;
		boolean bool=false;
		try{	
		
				statement = connection.prepareStatement("UPDATE event SET seatsAvailable=seatsAvailable-1 WHERE idEvent=?");
				statement.setInt(1, e.getIdEvent());
				affectedRows = statement.executeUpdate();
						
			
		} catch (Exception ee) {
			ee.getMessage();
			return bool;

		} 
		if(affectedRows==1){
			bool=true;
		}
		return bool;
		
	}

public boolean updateSumSeatsAvailableEventDAO(EventSignUp e){
	
	int affectedRows = 0;
	boolean bool=false;
	try{	
		
		statement = connection.prepareStatement("UPDATE event SET seatsAvailable=seatsAvailable+1 WHERE idEvent=?");
		statement.setInt(1, e.getIdEvent());
		affectedRows = statement.executeUpdate();
		
	} catch (Exception ee) {
		ee.getMessage();
		return bool;

	} 
	if(affectedRows==1){
		bool=true;
	}
	return bool;
	
}

public boolean checkSeatsAvailableDAO(EventSignUp e){
	
	boolean bool=false;
	try{	
		statement = connection.prepareStatement("SELECT seatsAvailable FROM event WHERE idEvent=?");
		statement.setInt(1, e.getIdEvent());
		ResultSet rs = statement.executeQuery();
		rs.next();
		
		if(rs.getInt("seatsAvailable")>0)
		{
			return true;
		}
		else{
			return false;
		}
		
		
		
	} catch (Exception ee) {
		ee.getMessage();
		return bool;

	} 
	
	
}

public boolean unregisterAllVisitorforEvent(int idEvent) throws ClassNotFoundException, SQLException {
	boolean bool=false;
	int affectedRows = 0;			 			
	try {
		//statement = connection.prepareStatement(json.getDeleteEvent());
		statement = connection.prepareStatement("DELETE from eventsignup where idEvent=?");
	    statement.setInt(1, idEvent);
	    
	    affectedRows = statement.executeUpdate();
	    
	} catch(SQLException ex) {
		System.out.println(ex.getMessage());
	}
	if(affectedRows>=0){
		bool=true;
	}
	return bool;
}
	
	
}
